package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * PersonDao的具体实现类
 * @author lamp
 *
 */
public class PersonDaoImpl implements PersonDao{

    /**
     * 实现添加方法
     */
    @Override
    public void add(Person p) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into person(name,age,description)values(?,?,?)";
        try{
        	conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, p.getStunum());
            ps.setString(2, p.getName());
            ps.setInt(3, p.getClasses());
            ps.setString(4, p.getGender());
            ps.setDouble(5, p.getJavaScore());
            ps.setDouble(6, p.getMathScore());
            ps.setDouble(7, p.getEnglishScore());
            ps.executeUpdate();
            System.out.println("添加成功！");
        }catch(SQLException e){
            e.printStackTrace();
            throw new SQLException("添加数据失败");
        }finally{
            DBUtils.close(null, ps, conn);
        }
    }

    /**
     * 更新方法
     */
    @Override
    public void update(Person p) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "update students set javaScore=?,mathScore=?,englishScore=? where stunum=?";
        try{
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            
	        ps.setDouble(1,p.getJavaScore());       
	        ps.setDouble(2,p.getMathScore());
	        ps.setDouble(3,p.getEnglishScore());
	        ps.setString(4, p.getStunum());
	        
	        ps.executeUpdate();
          System.out.println("修改成功！");
            ps.executeUpdate();
            System.out.println("修改成功！");
        }catch(SQLException e){
            e.printStackTrace();
            throw new SQLException("更新数据失败");
        }finally{
            DBUtils.close(null, ps, conn);
        }        
    }

    /**
     * 删除方法
     */
    @Override
    public void delete(String stunum) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "delete from person where stunum=?";
        try{
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,stunum);   //stunum为传入的需要删除的数据
            ps.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
            throw new SQLException(" 删除数据失败");
        }finally{
            DBUtils.close(null, ps, conn);
        }        
    }

   

    /**
     * 查询所有数据
     */
    @Override
    public List<Person> findAll() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Person p = null;
        List<Person> persons = new ArrayList<Person>();
        int classes=1;
        System.out.println("学号" + "\t" + "姓名"+ "\t" + "班级"+ "\t" + "性别"+ "\t" + "Java成绩"+ "\t" + "数学成绩"+ "\t" + "英语成绩");  
        System.out.println("-----------------");  
        String sql ="select * from students where classes like '%"+classes+"%'";//班级
        try{
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                p = new Person();
                p.setStunum(rs.getString("stunum"));
                p.setName(rs.getString("name"));
                p.setClasses(rs.getInt("classes"));
                p.setGender(rs.getString("gender"));
                p.setJavaScore(rs.getDouble("javaScore"));
                p.setMathScore(rs.getDouble("mathScore"));
                p.setEnglishScore(rs.getDouble("englishScore"));
                persons.add(p);
                
//                System.out.println(stunum+"\t"+name+"\t"+classes+"\t"+gender+"\t"+javaScore+
//            			"\t"+mathScore+"\t"+englishScore);//输出查询结果
            }
            System.out.println(persons);
        }catch(SQLException e){
            e.printStackTrace();
            throw new SQLException("查询所有数据失败");
        }finally{
            DBUtils.close(rs, ps, conn);
        }
        return persons;
    }

}